package com.sky.service.impl;

import com.sky.entity.Orders;
import com.sky.mapper.OrderDetailMapper;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.ReportService;
import com.sky.service.UserService;
import com.sky.vo.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.Map;
import java.util.Optional;
import java.util.StringJoiner;

@Service
@Slf4j
public class ReportServiceImpl implements ReportService {
    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private OrderDetailMapper orderDetailMapper;

    @Override
    public OrderStatisticsVO statistics() {

        // 创建一个OrderStatisticsVO对象，用于存储订单统计信息
        OrderStatisticsVO orderStatisticsVO = new OrderStatisticsVO();
        // 调用getPageCount方法，传入参数2，获取待确认状态的订单页数，并设置到orderStatisticsVO对象中
        orderStatisticsVO.setToBeConfirmed(getPageCount(2));
        // 调用getPageCount方法，传入参数3，获取已确认状态的订单页数，并设置到orderStatisticsVO对象中
        orderStatisticsVO.setConfirmed(getPageCount(3));
        // 调用getPageCount方法，传入参数4，获取配送中状态的订单页数，并设置到orderStatisticsVO对象中
        orderStatisticsVO.setDeliveryInProgress(getPageCount(4));
        // 返回填充好的OrderStatisticsVO对象
        return orderStatisticsVO;
    }

    private Integer getPageCount(Integer status) {
        return orderMapper.countByStatus(status);
    }

    @Override
    public TurnoverReportVO turnoverStatistics(LocalDate begin, LocalDate end) {
        LocalDate TempBegin = begin;
        LocalDate TempEnd = end;

        // 创建一个TurnoverReportVO对象，用于存储营业额统计报告
        TurnoverReportVO turnoverReportVO = new TurnoverReportVO();
        //日期dateList从begin到end，以逗号分隔，例如：2023-01-01,2023-01-02,2023-01-03
        StringJoiner dateSJ = new StringJoiner(",");
        while (begin.isBefore(end) || begin.isEqual(end)) {
            dateSJ.add(begin.toString());
            begin = begin.plusDays(1);
        }
        String dateList = dateSJ.toString();
        begin = TempBegin;
        end = TempEnd;

        //营业额，以逗号分隔，例如：406.0,1520.0,75.0
        StringJoiner turnoverSJ = new StringJoiner(",");
        String turnoverList = "";
        while (begin.isBefore(end) || begin.isEqual(end)) {
            turnoverSJ.add(orderMapper
                    .sumAmount(begin.atStartOfDay(), begin.atTime(LocalTime.MAX), Orders.COMPLETED)
                    .toString());
            begin = begin.plusDays(1);
        }
        turnoverList = turnoverSJ.toString();
        turnoverReportVO.setDateList(dateList);
        turnoverReportVO.setTurnoverList(turnoverList);
        return turnoverReportVO;
    }

    @Override
    public OrderReportVO ordersStatistics(LocalDate begin, LocalDate end) {
        LocalDate TempBegin = begin;
        LocalDate TempEnd = end;

        OrderReportVO orderReportVO = new OrderReportVO();

        //日期列表
        StringJoiner dateSJ = new StringJoiner(",");
        while (begin.isBefore(end) || begin.isEqual(end)) {
            dateSJ.add(begin.toString());
            begin = begin.plusDays(1);
        }
        String dateList = dateSJ.toString();
        begin = TempBegin;
        end = TempEnd;

        //有效订单数
        Integer validOrderCount = orderMapper.countByTimeAndStatus(begin.atStartOfDay(), end.atTime(LocalTime.MAX), Orders.COMPLETED);


        //订单列表
        StringJoiner orderCountSJ = new StringJoiner(",");
        String orderCountList = "";
        while (begin.isBefore(end) || begin.isEqual(end)) {
            orderCountSJ.add(orderMapper.countAll(begin.atStartOfDay(), begin.atTime(LocalTime.MAX)).toString());
            begin = begin.plusDays(1);
        }
        orderCountList = orderCountSJ.toString();
        begin = TempBegin;
        end = TempEnd;

        //订单总数
        Integer totalOrderCount = orderMapper.countAll(begin.atStartOfDay(), end.atTime(LocalTime.MAX));

        //订单完成率
        Double orderCompletionRate = totalOrderCount == 0 ? 0 : (double) validOrderCount / totalOrderCount * 100;


        //订单数列表
        StringJoiner ordersCountSJ = new StringJoiner(",");
        String ordersCountList = "";
        while (begin.isBefore(end) || begin.isEqual(end)) {
            ordersCountSJ.add(orderMapper.countAll(begin.atStartOfDay(), begin.atTime(LocalTime.MAX)).toString());
            begin = begin.plusDays(1);
        }
        orderCountList = ordersCountSJ.toString();

        orderReportVO.setDateList(dateList);
        orderReportVO.setOrderCountList(orderCountList);
        orderReportVO.setValidOrderCountList(ordersCountList);
        orderReportVO.setTotalOrderCount(totalOrderCount);
        orderReportVO.setValidOrderCount(validOrderCount);
        orderReportVO.setOrderCompletionRate(orderCompletionRate);
        return orderReportVO;

    }

    @Override
    public UserReportVO userStatistics(LocalDate begin, LocalDate end) {
        LocalDate TempBegin = begin;
        LocalDate TempEnd = end;

        //1.日期列表
        StringJoiner dateSJ = new StringJoiner(",");
        while (begin.isBefore(end) || begin.isEqual(end)) {
            dateSJ.add(begin.toString());
            begin = begin.plusDays(1);
        }
        String dateList = dateSJ.toString();
        begin = TempBegin;
        end = TempEnd;

        //2.总用户数列表
        StringJoiner totalUserSJ = new StringJoiner(",");
        String totalUserList = "";
        while (begin.isBefore(end) || begin.isEqual(end)) {
            totalUserSJ.add(userMapper.countTotal(begin.atTime(LocalTime.MAX)).toString());
            begin = begin.plusDays(1);
        }
        totalUserList = totalUserSJ.toString();
        begin = TempBegin;
        end = TempEnd;

        //3.新增用户数列表
        StringJoiner newUserSJ = new StringJoiner(",");
        String newUserList = "";
        while (begin.isBefore(end) || begin.isEqual(end)) {
            newUserSJ.add(userMapper.countByTime(begin.atStartOfDay(), begin.atTime(LocalTime.MAX)).toString());
            begin = begin.plusDays(1);
        }
        newUserList = newUserSJ.toString();


        UserReportVO userReportVO = new UserReportVO();
        userReportVO.setDateList(dateList);
        userReportVO.setTotalUserList(totalUserList);
        userReportVO.setNewUserList(newUserList);
        return userReportVO;
    }

    @Override
    public SalesTop10ReportVO top10(LocalDate begin, LocalDate end) {
        LocalDate TempBegin = begin;
        LocalDate TempEnd = end;

        StringJoiner nameSJ = new StringJoiner(",");
        String nameList = "";
        StringJoiner numberSJ = new StringJoiner(",");
        String numberList = "";
        while (begin.isBefore(end) || begin.isEqual(end)) {
            Map<String, Integer> map = orderDetailMapper.selectTop10(begin.atStartOfDay(), begin.atTime(LocalTime.MAX));
            begin = begin.plusDays(1);
            for (Map.Entry<String, Integer> entry : map.entrySet()) {
                nameSJ.add(entry.getKey());
                numberSJ.add(entry.getValue().toString());
            }
        }
        nameList = nameSJ.toString();
        numberList = numberSJ.toString();
        SalesTop10ReportVO salesTop10ReportVO = new SalesTop10ReportVO();
        salesTop10ReportVO.setNameList(nameList);
        salesTop10ReportVO.setNumberList(numberList);
        return salesTop10ReportVO;
    }



    public void export(HttpServletResponse response) {
        LocalDate begin = LocalDate.now().minusDays(30);
        LocalDate end = LocalDate.now();
        LocalDateTime startTime = begin.atStartOfDay();
        LocalDateTime endTime = end.atTime(LocalTime.MAX);

        ArrayList list = getData(startTime, endTime);


        // 资源初始化
        InputStream templateStream = null;
        XSSFWorkbook workbook = null;
        try {
            // 加载模板文件
            templateStream = this.getClass().getResourceAsStream("/template/运营数据报表模板.xlsx");
            // 创建Excel工作簿
            workbook = new XSSFWorkbook(templateStream);
            XSSFSheet sheet = workbook.getSheet("Sheet1");

            // 填充基础信息
            setCellValue(sheet, 1, 1, "日期范围：" + begin + "至" + end);

            // 填充统计概览
            setCellValue(sheet, 3, 2, String.format("%.2f", list.get(0)));
            setCellValue(sheet, 3, 4, String.format("%.2f%%", list.get(3)));
            setCellValue(sheet, 3, 6, list.get(5).toString());
            setCellValue(sheet, 4, 2,list.get(1).toString());
            setCellValue(sheet, 4, 4, String.format("%.2f", list.get(4)));

            //30日内订单明细数据
            for (int i = 0; i < 30; i++) {
                LocalDate date = begin.plusDays(i);
                LocalDateTime start = date.atStartOfDay();
                LocalDateTime end1 = date.atTime(LocalTime.MAX);



                ArrayList list2 = getData(start, end1);
                Double turnover1 = (Double) list2.get(0);
                Integer validOrderCount1 = (Integer) list2.get(1);
                Integer totalOrderCount1 = (Integer) list2.get(2);
                Double orderCompletionRate1 = (Double) list2.get(3);
                Double avgOrderPrice1 = (Double) list2.get(4);
                Integer newUserCount1 = (Integer) list2.get(5);

                // 填充订单明细数据
                // 日期
                setCellValue(sheet, 7 + i, 1,date.toString() );
                // 营业额
                setCellValue(sheet, 7 + i, 2, String.format("%.2f", turnover1));
                // 有效订单数
                setCellValue(sheet, 7 + i, 3, validOrderCount1.toString());
                // 订单完成率
                setCellValue(sheet, 7 + i, 4, String.format("%.2f%%", orderCompletionRate1));
                // 平均订单价格
                setCellValue(sheet, 7 + i, 5, String.format("%.2f", avgOrderPrice1));
                // 新增用户数
                setCellValue(sheet, 7 + i, 6, newUserCount1.toString());
            }


            // 写入响应流
            try (ServletOutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
            }
        } catch (Exception e) {

        } finally {
            // 资源关闭
            try {
                if (workbook != null) {
                    workbook.close();
                }
            } catch (IOException e) {

            }
            try {
                if (templateStream != null) {
                    templateStream.close();
                }
            } catch (IOException e) {

            }
        }
    }

    // 单元格填充辅助方法
    private void setCellValue(XSSFSheet sheet, int rowNum, int colNum, String value) {
        XSSFRow row = Optional.ofNullable(sheet.getRow(rowNum)).orElseGet(() -> sheet.createRow(rowNum));
        XSSFCell cell = Optional.ofNullable(row.getCell(colNum)).orElseGet(() -> row.createCell(colNum));
        cell.setCellValue(value);
    }

    private ArrayList getData(LocalDateTime startTime, LocalDateTime endTime) {
        // 数据查询（包含空值处理）
        Double turnover = Optional.ofNullable(orderMapper.sumAmount(startTime, endTime, Orders.COMPLETED)).orElse(0.0);
        Integer validOrderCount = Optional.ofNullable(orderMapper.countByTimeAndStatus(startTime, endTime, Orders.COMPLETED)).orElse(0);
        Integer totalOrderCount = Optional.ofNullable(orderMapper.countAll(startTime, endTime)).orElse(0);
        Double orderCompletionRate = totalOrderCount == 0 ? 0.0 : (double) validOrderCount / totalOrderCount * 100;
        Double avgOrderPrice = validOrderCount == 0 ? 0.0 : turnover / validOrderCount;
        Integer newUserCount = Optional.ofNullable(userMapper.countByTime(startTime, endTime)).orElse(0);

        ArrayList list = new ArrayList();
        list.add(turnover);
        list.add(validOrderCount);
        list.add(totalOrderCount);
        list.add(orderCompletionRate);
        list.add(avgOrderPrice);
        list.add(newUserCount);
        return list;
    }

}



